﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Dev01.DataAccsess
{
    public class DataService : DataTable
    {
        public static SqlConnection m_Connection;
        private SqlDataAdapter m_DataAdapter;
        private SqlCommand m_Command;

        public static String m_ConnectionString = "Data Source=htphong-pc\\sqlexpress;Initial Catalog=PRACTICE1;Integrated Security=True";

        public DataService()
        {
            OpenConnection();
        }

        public SqlCommand Command
        {
            get { return m_Command; }
            set { m_Command = value; }
        }

        public void Load(SqlCommand command)
        {
            m_Command = command;
            try
            {
                this.Clear();

                m_Command.Connection = m_Connection;

                m_DataAdapter = new SqlDataAdapter();
                m_DataAdapter.SelectCommand = m_Command;
                m_DataAdapter.Fill(this);


            }
            catch (Exception e)
            {
                throw;
                //MessageBox.Show(e.Message);
            }
        }

        public static bool OpenConnection()
        {
            try
            {
                if (m_Connection == null)
                    m_Connection = new SqlConnection(m_ConnectionString);


                if (m_Connection.State == ConnectionState.Closed)
                    m_Connection.Open();
                return true;
            }
            catch (Exception e)
            {
                m_Connection.Close();
                return false;
            }

        }
        /// <summary> 
        /// Closes the connection of this data service. 
        /// </summary> 
        public void CloseConnection()
        {
            m_Connection.Close();
        }

        /// <summary> 
        /// Update DataTable 
        /// </summary> 
        /// <returns></returns> 
        public int ExecuteNoneQuery()
        {

            int result = 0;
            SqlTransaction tr = null;
            // m_Dataset = new DataSet(); 
            try
            {
                tr = m_Connection.BeginTransaction();

                m_Command.Connection = m_Connection;
                m_Command.Transaction = tr;

                m_DataAdapter = new SqlDataAdapter();
                m_DataAdapter.SelectCommand = m_Command;

                SqlCommandBuilder builder = new SqlCommandBuilder(m_DataAdapter);

                result = m_DataAdapter.Update(this);
                tr.Commit();

            }
            catch (SqlException e)
            {
                if (tr != null) tr.Rollback();
                DisplayError(e);
            }
            //System.Windows.Forms.MessageBox.Show(result.ToString()); 
            return result;
        }
        /// <summary> 
        /// Thuc thi mot commandh 
        /// </summary> 
        /// <param name="command">OleDb hay Store Procedure</param> 
        /// <returns></returns> 
        public int ExecuteNoneQuery(SqlCommand cmd)
        {

            int result = 0;
            SqlTransaction tr = null;

            try
            {
                tr = m_Connection.BeginTransaction();

                cmd.Connection = m_Connection;

                cmd.Transaction = tr;

                result = cmd.ExecuteNonQuery();

                this.AcceptChanges();

                tr.Commit();

            }
            catch (SqlException e)
            {
                if (tr != null) tr.Rollback();
                DisplayError(e);
                //throw;
            }
            return result;

        }

        public object ExecuteScalar(SqlCommand cmd)
        {
            object result = null;
            SqlTransaction tr = null;

            try
            {
                tr = m_Connection.BeginTransaction();

                cmd.Connection = m_Connection;

                cmd.Transaction = tr;

                result = cmd.ExecuteScalar();

                this.AcceptChanges();

                tr.Commit();

                if (result == DBNull.Value)
                {
                    result = null;
                }
            }
            catch
            {
                if (tr != null) tr.Rollback();
                throw;
            }
            return result;
        }

        #region Kiểm Tra
        private void DisplayError(SqlException ex)
        {
            string message;
            switch (ex.Number)
            {
                case 17:
                    message = "Server không đúng !";
                    break;
                case 4060:
                    message = "Cơ sở dữ liệu không đúng !";
                    break;
                case 18456:
                    message = "Không đúng tên truy nhập và mật khẩu !";
                    break;
                case 547:
                    message = "Vi phạm khoá ngoài, bạn không thể thực hiện!";
                    break;
                case 2627:
                case 2601:
                    message = "Trùng giá trị trường khoá";
                    break;
                case 8152:
                    message = "Dữ liệu nhập quá dài";
                    break;
                default:
                    message = ex.Message;
                    break;
            }
            System.Windows.Forms.MessageBox.Show(message, "Error " + ex.Number.ToString());
        }
        #endregion


    }
}
